#!/bin/bash
#获取系统时间，格式:yyyymmddhhmmss
sys_time=`date +%Y-%m-%d`
 
#临时表名
temp_tabname='temp_dwd_distribution_income'

##############################################################                  
#  当天一级收入，当天二级收入，当天三级收入，当天总收入，同比，环比,本周总收入，本月总收入，累计收入
##############################################################

#清除历史数据
connkpi -e "delete from dwd_distribution_income
             where op_time ='${sys_time}'
           "
#判断前句命令执行是否成功 0为成功,非0为失败.
if [ $? != 0 ]; then
    exit 1
fi

#生成当天最新数据
connkpi -e "drop table ${temp_tabname}"
# op_time,userId,username,当天一级收入，当天二级收入，当天三级收入，当天总收入
connkpi -e "create table ${temp_tabname}
            (
              op_time datetime DEFAULT NULL,
              userId bigint(20) DEFAULT NULL,
              username varchar(23) DEFAULT NULL,
              current_day_one int(11) DEFAULT NULL,
              current_day_two int(11) DEFAULT NULL,
              current_day_three int(11) DEFAULT NULL,
              current_day int(11) DEFAULT NULL
            ) "
#判断前句命令执行是否成功 0为成功,非0为失败.
if [ $? != 0 ]; then
    exit 1
fi
echo "建表成功"
     
##  当天一级收入
#######################################################################
    connkpi -e "insert into ${temp_tabname} (op_time,userId,username,current_day_one) 
                       SELECT DATE(b.ORDERVALIDDATE) op_time,a.id userId,a.loginname,SUM(b.totalprice)*0.15 current_day FROM base_user a
                       INNER JOIN base_order b ON a.id=b.usercode
                       WHERE b.orderstate='已结单' and date(ORDERVALIDDATE)='${sys_time}'
                       GROUP BY  b.ORDERVALIDDATE,a.id,a.loginname;
                   "
    #判断前句命令执行是否成功 0为成功,非0为失败.
    if [ $? != 0 ]; then
        exit 1
    fi
echo "插入天一级收入"

##  当天二级收入
#######################################################################
    connkpi -e "insert into ${temp_tabname} (op_time,userId,username,current_day_two) 
                       SELECT DATE(b.ORDERVALIDDATE) op_time,a.id userId,a.loginname,SUM(b.totalprice)*0.11 current_day FROM base_user a
                       INNER JOIN base_order b ON a.id=b.OPERATOR
                       WHERE b.orderstate='已结单' and date(ORDERVALIDDATE)='${sys_time}'
                       GROUP BY  b.ORDERVALIDDATE,a.id,a.loginname;
                   "
    #判断前句命令执行是否成功 0为成功,非0为失败.
    if [ $? != 0 ]; then
        exit 1
    fi
    
echo "插入天二级收入"

#  当天三级收入
#######################################################################
    connkpi -e "insert into ${temp_tabname} (op_time,userId,username,current_day_three) 
                      SELECT DATE(b.ORDERVALIDDATE) op_time,a.id userId,a.loginname,SUM(b.totalprice)*0.08 current_day FROM base_user a
                      INNER JOIN base_userrelation re ON a.id=re.parent
                      INNER JOIN base_order b ON b.OPERATOR=re.USERCODE
                      WHERE b.orderstate='已结单' and date(ORDERVALIDDATE)='${sys_time}'
                      GROUP BY  b.ORDERVALIDDATE,a.id,a.loginname;
                   "
    #判断前句命令执行是否成功 0为成功,非0为失败.
    if [ $? != 0 ]; then
        exit 1
    fi 
echo "当天三级收入"

    connkpi -e "insert into dwd_distribution_income
                      select  '${sys_time}' op_time,
                             a.userId,
                             a.username,
                             a.current_day_one,
                             a.current_day_two,
                             a.current_day_three,
                             a.current_day,
                             case when b.op_time=adddate('${sys_time}',interval -1 year) then  (a.current_day - b.current_day)/b.current_day end tb,  -- 同比
                             case when b.op_time=adddate('${sys_time}',interval -1 day) then (a.current_day - b.current_day)/b.current_day end hb,  -- 环比
                             case when date_format(b.op_time,'yyyymm')=date_format(a.op_time,'yyyymm') then b.current_month + a.current_day end current_month, -- 当月
                             ifnull(b.lj,0)+a.current_day lj
                      from (            
                            select
                                op_time,
                                userId,
                                username,
                                max(current_day_one) current_day_one,
                                max(current_day_two) current_day_two,
                                max(current_day_three) current_day_three,
                                max(current_day_one) + max(current_day_two) + max(current_day_three) current_day
                            from ${temp_tabname}
                            group by op_time,userId,username
                           ) a
                      LEFT JOIN dwd_distribution_income b on (a.userId=b.userId)
                 where b.op_time <='${sys_time}'  
                  "
    #判断前句命令执行是否成功 0为成功,非0为失败.
    if [ $? != 0 ]; then
        exit 1
    fi
  
connkpi -e "drop table ${temp_tabname}"
echo "删除临时表，数据更新成功。"
exit 0